#!/usr/bin/env python
# -*- coding: utf-8 -*-
import mysql.connector
import db_util as db

def create_table():
    conn = db.get_connector()
    sql_str = '''
                CREATE TABLE `hot_topic` (
                `tid` int(11) NOT NULL AUTO_INCREMENT ,
                `date` varchar(8) NOT NULL, 
                `topic` varchar(50) NOT NULL, 
                `frequency` int(5) NOT NULL, 
                PRIMARY KEY (`tid`)
                )
                '''
    db.op_table(conn, sql_str)
    db.close_connector(conn)

def drop_table():
    conn = db.get_connector()
    sql_str = '''
                DROP TABLE `hot_topic`
                '''
    db.op_table(conn, sql_str)
    db.close_connector(conn)
    
def insert(sql_data):
    conn = db.get_connector()
    sql_str = ("INSERT INTO hot_topic (date, topic, frequency) VALUES (%s, %s, %s)")
#     sql_data = ('20151001', 'TOPIC1', 50)
    db.insert(conn, sql_str, sql_data)
    db.close_connector(conn)
    
def query_by_date(sql_data):
    conn = db.get_connector()
    sql_str = ("SELECT topic, frequency FROM hot_topic WHERE date=%s ORDER BY frequency DESC LIMIT %s")
#     sql_data = ('20151001')
    cursor = db.query(conn, sql_str, sql_data)
    result = []
    for (topic, frequency) in cursor:
        result.append([topic, frequency])
    db.close_connector(conn)
    return result

def query_between_date(sql_data):
    conn = db.get_connector()
    sql_str = ("SELECT date, IFNULL(frequency, '0') FROM hot_topic WHERE topic=%s AND date>=%s AND date<=%s ORDER BY date")
#     sql_data = ('TOPIC1', '20151001', '20151031')
    cursor = db.query(conn, sql_str, sql_data)
    result = []
    for (date, frequency) in cursor:
        result.append([date, frequency])
    db.close_connector(conn)
    return result

def init_data():
    for date_num in range(20151001, 20151032):
        print date_num
        input_file ='/home/ivy/git/Social/Social/resource/hot_topic/%d.txt' % date_num
        with open(input_file, 'r+') as f:
            counter = 0
            for line in f:
                if counter == 100:
                    break
                arr = line.strip().split('\t')
                sql_data = (str(date_num), arr[0], arr[1])  
                insert(sql_data) 
                counter += 1
        f.close()

def main():

    drop_table()
    create_table()
    init_data()
    
    
if __name__ == '__main__':
    main()
        